package com.walle.ak47.commons.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCountCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.walle.ak47.commons.utils.StringUtils;
import com.walle.ak47.commons.utils.json.GBuilder;


public class SmartCommonDao {
		
	private static Logger LOG = LoggerFactory.getLogger(SmartCommonDao.class);
	
	JdbcTemplate jdbcTemplate ;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public boolean hashColumnName( String sql, String colName )
	{
		RowCountCallbackHandler rcch = new RowCountCallbackHandler();
		jdbcTemplate.query( sql, rcch);
		System.out.println("结果集中的列数量:"+rcch.getColumnCount());
		System.out.println("结果集中的行数量:"+rcch.getRowCount());
		System.out.println("结果集中的列名:");
		String[] str= rcch.getColumnNames();
		for(int i = 0;i<str.length;i++){
			System.out.println(str[i]);
		}
		
		return false ;
	}
	
	public int save( String tableName, final Map<String,Object> attrs ) 
	{
		final List<Object> paras = new ArrayList<Object>() ;
		final StringBuilder sql = new StringBuilder() ;
		sql.append("insert into ").append( tableName ).append("(");
		StringBuilder temp = new StringBuilder(") values(");
		for (Entry<String, Object> e: attrs.entrySet()) {
			String colName = e.getKey();
			if (paras.size() > 0) {
				sql.append(", ");
				temp.append(", ");
			}
			sql.append(colName);
			temp.append("?");
			paras.add(e.getValue()) ;
		}
		sql.append(temp.toString()).append(")");
		
		
		LOG.debug( "Save->SQL:"+sql.toString()  );
		LOG.debug( "Save->Paras:"+GBuilder.toJson(attrs) );
		
		KeyHolder keyHolder = new GeneratedKeyHolder();  
		int size = 0 ;
		try
		{
			size = jdbcTemplate.update(new PreparedStatementCreator() {  
			        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
			              
			        	PreparedStatement ps = connection.prepareStatement(sql.toString(),Statement.RETURN_GENERATED_KEYS);  
			        	for ( int i=1; i<=paras.size(); i++ ) {
			        		ps.setObject( i, paras.get(i-1));
			        	}
			        	return ps;  
			        }  
			    }, keyHolder);  
		}
		catch( Exception ex )
		{
			ex.printStackTrace();
		}
		Integer generatedId = keyHolder.getKey().intValue() ;
		
		for( Entry<String, Object> entry: this.findById(tableName, generatedId).entrySet() )
			attrs.put( entry.getKey(), entry.getValue() ) ;
		
		return size ;
	}
	
	public int deleteById( String tableName, Object id )
	{
		StringBuilder sql = new StringBuilder() ;
		sql.append( "update " ).append(tableName).append( " delete from ").append( tableName ).append(" Id=? ") ;
		
		LOG.debug( "DeleteById->SQL:"+sql.toString()  );
		LOG.debug( "DeleteById->Paras:{tableName:"+tableName+",id:"+id+"}" );
		
		int size = 0 ;
		try
		{
			size = jdbcTemplate.update( sql.toString(), id ) ;
		}
		catch( Exception ex )
		{
			ex.printStackTrace(); 
		}
		return size ;
	}
	
	public int updateSkipNull( String tableName, final Map<String,Object> attrs ) throws Exception
	{
		String primaryKey = "id" ;
		if ( attrs.get(primaryKey) == null )
			 throw new Exception("talbe="+tableName+" primaryKey is null") ;
		
		final List<Object> paras = new ArrayList<Object>() ;
		final StringBuilder sql = new StringBuilder() ;
		sql.append("update ").append(tableName).append(" set ");
		for (Entry<String, Object> e : attrs.entrySet()) {
			String colName = e.getKey();
			Object val = e.getValue() ;
			if (!primaryKey.equalsIgnoreCase(colName) && val!=null ) {
				if (paras.size() > 0)
					sql.append(", ");
				sql.append(colName).append(" = ? ");
				paras.add(e.getValue());
			}
		}
		sql.append(" where ").append(primaryKey).append(" = ?");
		paras.add( attrs.get(primaryKey) );
		
		LOG.debug( "Update->SQL:"+sql.toString()  );
		LOG.debug( "Update->Paras:"+GBuilder.toJson(attrs) );
		
		int size = 0;
		try
		{
			size = jdbcTemplate.update(sql.toString(), new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					for( int i=1; i<=paras.size(); i++ )
						ps.setObject( i, paras.get(i-1) );
				}
			});
		}
		catch( Exception ex )
		{
			ex.printStackTrace();
		}
		
		Map<String,Object> result = this.findById(tableName, attrs.get(primaryKey));
		if ( result==null )
		{
			attrs.clear();
		}
		else
		{
			for( Entry<String, Object> entry: result.entrySet() )
				attrs.put( entry.getKey(), entry.getValue() ) ;
		}
		
		return size ;
	}
	
	public int update( String tableName, final Map<String,Object> attrs ) throws Exception
	{
		String primaryKey = "id" ;
		if ( attrs.get(primaryKey) == null )
			 throw new Exception("talbe="+tableName+" primaryKey is null") ;
		
		final List<Object> paras = new ArrayList<Object>() ;
		final StringBuilder sql = new StringBuilder() ;
		sql.append("update ").append(tableName).append(" set ");
		for (Entry<String, Object> e : attrs.entrySet()) {
			String colName = e.getKey();
			if (!primaryKey.equalsIgnoreCase(colName) ) {
				if (paras.size() > 0)
					sql.append(", ");
				sql.append(colName).append(" = ? ");
				paras.add(e.getValue());
			}
		}
		sql.append(" where ").append(primaryKey).append(" = ?");
		paras.add( attrs.get(primaryKey) );
		
		LOG.debug( "Update->SQL:"+sql.toString()  );
		LOG.debug( "Update->Paras:"+GBuilder.toJson(attrs) );
		
		int size = 0;
		try
		{
			size = jdbcTemplate.update(sql.toString(), new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					for( int i=1; i<=paras.size(); i++ )
						ps.setObject( i, paras.get(i-1) );
				}
			});
		}
		catch( Exception ex )
		{
			ex.printStackTrace();
		}
		
		Map<String,Object> result = this.findById(tableName, attrs.get(primaryKey));
		if ( result==null )
		{
			attrs.clear();
		}
		else
		{
			for( Entry<String, Object> entry: result.entrySet() )
				attrs.put( entry.getKey(), entry.getValue() ) ;
		}
		
		return size ;
	}
	
	public Map<String,Object> findById( String tableName , Object id ) {
		
		StringBuilder sql = new StringBuilder() ;
		sql.append( "select * from ").append( tableName )
			.append( " where ").append("Id=?") ;
		
		LOG.debug( "FindById->Sql:"+sql.toString()  );
		LOG.debug( "FindById->Paras:{tableName:"+tableName+",id:"+id+"}" );
		
		Map<String,Object> result = null ;
		try
		{
			result = jdbcTemplate.queryForMap(sql.toString(), id ) ;
		}
		catch( EmptyResultDataAccessException ex )
		{
			return null ;
		}
		catch( Exception ex )
		{
			ex.printStackTrace();
		}
		
		return  result;
	}
	
	public List<Map<String,Object>> find( String sql, Object... paras) {
		
		sql = sql.toLowerCase().trim() ;
		sql = new StringBuffer(sql).toString() ;
		
		List<Map<String,Object>> result = null ;
		
		try
		{
			
			
			LOG.debug( "Find->SQL:"+sql.toString()  );
			LOG.debug( "Find->Paras:"+GBuilder.toJson(paras) );
			
			result = jdbcTemplate.queryForList( sql, paras) ;
		}
		catch( Exception ex )
		{
			ex.printStackTrace();
		}
		
		return  result;
	}
	
	@SuppressWarnings({ "deprecation" })
	public long count( String sql, Object... paras )
	{
		long count = 0 ;
		
		sql = sql.toLowerCase().trim() ;
		
		StringBuilder countSql = new StringBuilder() ;
		countSql.append( "select count(0) from ") ;
		countSql.append( replaceFormatSqlOrderBy(StringUtils.split( sql, "from ")[1]) );
		
		
		LOG.debug( "Count->SQL:"+countSql.toString()  );
		LOG.debug( "Count->Paras:"+GBuilder.toJson(paras) );
		
		try
		{
			count = jdbcTemplate.queryForLong( countSql.toString(), paras ) ;
		}
		catch( Exception ex )
		{
			ex.printStackTrace();
		}
		return count ;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Page paginateForMysql( int pageNumber, int pageSize, String sql, Object... paras) throws Exception
	{
		sql = sql.toLowerCase().trim() ;
		if (pageNumber < 1 || pageSize < 1)
			throw new Exception("pageNumber and pageSize must be more than 0");
		
		long totalRow = 0;
		int totalPage = 0;
		totalRow = this.count(sql, paras) ;
		if (totalRow <= 0 )
			return new Page(new ArrayList(0), pageNumber, pageSize, 0, 0);	 
		
		// 总页数算法
		totalPage = (int) (totalRow / pageSize);
		if (totalRow % pageSize != 0) {
			totalPage++;
		}

		int offset = pageSize * (pageNumber - 1);
		sql = new StringBuffer().append(" limit ").append(offset).append(", ").append(pageSize).toString();	
		
		List<Map<String,Object>> list = null ;
		
		LOG.debug( "PaginateForMysql->SQL:"+sql );
		LOG.debug( "PaginateForMysql->Paras:"+GBuilder.toJson(paras) );
		
		try
		{
			list = jdbcTemplate.queryForList( sql, paras ) ;
		}
		catch( Exception ex )
		{
			ex.printStackTrace(); 
		}
		
		return new Page(list, pageNumber, pageSize, totalPage, (int)totalRow);
	}
	
	protected String replaceFormatSqlOrderBy(String sql) {
		sql = sql.replaceAll("(\\s)+", " ");
		int index = sql.toLowerCase().lastIndexOf("order by");
		if (index > sql.toLowerCase().lastIndexOf(")")) {
			String sql1 = sql.substring(0, index);
			String sql2 = sql.substring(index);
			sql2 = sql2.replaceAll("[oO][rR][dD][eE][rR] [bB][yY] [\u4e00-\u9fa5a-zA-Z0-9_.]+((\\s)+(([dD][eE][sS][cC])|([aA][sS][cC])))?(( )*,( )*[\u4e00-\u9fa5a-zA-Z0-9_.]+(( )+(([dD][eE][sS][cC])|([aA][sS][cC])))?)*", "");
			return sql1 + sql2;
		}
		return sql;
	}
}
